
#!/bin/bash
mysql --user=root --password=project3 << EOF
CREATE DATABASE IF NOT EXISTS implauth;
USE implauth;

CREATE TABLE IF NOT EXISTS servers (
  id char(40) primary key,
  secretkey char(40) DEFAULT NULL,
  email text,
  phone char(10) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS hdvid_appkey(
  hdvid char(40) primary key, 
  appkey char(40),
  KEY (appkey));

CREATE TABLE IF NOT EXISTS accounts (
  serverid char(40) NOT NULL,
  FOREIGN KEY (serverid) references servers(id) on delete cascade on update cascade,
  hun char(40),
  hdeviceid char(40) NOT NULL,
  FOREIGN KEY (hdeviceid) references hdvid_appkey(hdvid) on delete cascade on update cascade,
  appkey char(40),
  FOREIGN KEY (appkey) references hdvid_appkey(appkey) on delete cascade on update cascade,
  PRIMARY KEY (serverid),
  KEY (hdeviceid, appkey),
  UNIQUE KEY accounts_idx1 (serverid, hun)
);

CREATE TABLE IF NOT EXISTS accountstba (
  serverid char(40), 
  FOREIGN KEY (serverid) references servers(id) on delete cascade on update cascade,
  code char(40) primary key,
  hun char(40),
  UNIQUE KEY accountstba_serverid_idx (serverid)
);

CREATE TABLE IF NOT EXISTS accountstbr (
  serverid char(40), 
  FOREIGN KEY (serverid) references servers(id) on delete cascade on update cascade,
  code char(40) primary key,
  hun char(40),
  UNIQUE KEY accountstbr_serverid_idx (serverid)
);

CREATE TABLE IF NOT EXISTS serverstba (
  code char(40) NOT NULL,
  email text,
  phone char(10) DEFAULT NULL,
  PRIMARY KEY (code)
);

CREATE TABLE IF NOT EXISTS locations (
  hdeviceid char(40) primary key,
  FOREIGN KEY (hdeviceid) references hdvid_appkey(hdvid) on delete cascade on update cascade,
  latitude double DEFAULT NULL,
  longitude double DEFAULT NULL,
  accuracy double DEFAULT NULL,
  time bigint(20) unsigned DEFAULT NULL,
  stopped char(1)
);
EOF
exit 0;
